<?

require_once '../include/common.inc.php';
//error_reporting(E_ALL);

if (!checkAdmin()) {
    header("LOCATION:index.php");
    exit;
}

$menu_index = 1;
$page_title = 'JANSEN';
$guide_message = 'Product | Basic Infomation | Import Data';

getMyParam('importtype');

$error_str = '';
$upload_file = "";


if ($importtype == '1' || $importtype == '2') {
		
    if(strtolower(getExt($_FILES['upload_file']['name'])) == "xls" && $_FILES['upload_file']['size'] > 0 && ($_FILES['upload_file']['type'] == "application/vnd.ms-excel" || $_FILES['upload_file']['type'] == "application/octet-stream")){
        $upload_file = $_FILES['upload_file']['tmp_name'];

        // =============== excelתΪ start=======================
        $user_method = 1;
        require_once '../include/reader.php';
        $data = new Spreadsheet_Excel_Reader();
        //$data->setOutputEncoding('CP1251');
        $data->setOutputEncoding('gb2312');
        $data->read($upload_file);
        $tmp_ary = $data->sheets[0]['cells'];

        if (is_array($tmp_ary) || $tmp_ary[1][1] == 'Item#') {
            foreach ($tmp_ary as $k1=>$v1) {
                $tmp2_ary = array();
                foreach ($v1 as $k2=>$v2) {
                    $tmp2_ary[$k2-1] = $v2;
                }
                $excel_ary[$k1-1] = $tmp2_ary;
            }
        } else {
            $excel_ary = '';
        }
        // 2:ʹ execl.class.php
        if (!is_array($excel_ary) || $excel_ary[0][0] != 'Item#') {
            $user_method = 2;
            require_once '../include/excel.class.php';
            Read_Excel_File($upload_file, $return);

            if (is_array($return)) {
                $index = 0;
                foreach ($return as $k=>$v) {
                    if ($index == 0) {
                        $excel_ary = $v;
                    }
                    $index++;
                }
            } else {
                $excel_ary = '';
            }
        }
        // 3:phpɵexecl
        if (!is_array($excel_ary) || $excel_ary[0][0] != 'Item#') {
            $user_method = 3;
            $excel_ary = array();
            $excel_ary1 = file($upload_file);

            foreach ($excel_ary1 as $k=>$v) {
                $tmp_ary = explode("\t", $v);
                $excel_ary[$k] = $tmp_ary;
            }
        }
        // =============== excelתΪ end=======================

        if ($excel_ary[0][0] != 'Item#') {   // Ψһ
            $error_str .= "<div class='js_msg'>excel error in line: code</div>";
        }

    } elseif ($_FILES['upload_file']['size'] > 0) {
        $error_str .= "<div class='js_msg'>excel file error</div>";
    }

    
    // excelкַ''ʱʾĴ 
    /*
    $string = htmlentities($excel_ary[1][1]);
    if (strstr($string, '&iexcl;&macr;')) {
        //$string = str_replace('&iexcl;&macr;', '\'', $string);echo $string;exit;
        $string = str_replace('&iexcl;&macr;', '', $string);echo $string;exit;
    } 
    
    $string = preg_replace('~&#x([0-9a-f]+);~ei', 'chr(hexdec("\\1"))', $string);
    $string = preg_replace('~&#([0-9]+);~e', 'chr(\\1)', $string); 
    echo $string;exit;
    //echo $excel_ary[1][1];exit;
    //echo strip_tags($excel_ary[1][1]);exit;
    //$excel_ary[1][1] = html_entity_decode($excel_ary[1][1], ENT_QUOTES);echo $excel_ary[1][1];
    $excel_ary[1][1] = hexdec($excel_ary[1][1]);echo $excel_ary[1][1];
    str_replace("&iexcl;&macr;", ":", $excel_ary[1][1]);echo $excel_ary[1][1];exit;
    */
    
    
    
    //echo $user_method; echo "<hr>"; echo $error_str; echo "<hr>"; echo "<pre>";print_r($excel_ary);exit;

}

if ($importtype == '1' && $error_str == "") {

    // ==================== ж start ==============================

    // вƷж
    $product_hava_ary = array();
    $sql = "SELECT `id`, `code` FROM `product`";
    $query = $db->query($sql);
    while($rs = $db->fetch_array($query)) {
        $rs['code'] = trim(strtolower($rs['code']));
        $product_hava_ary[$rs['code']] = $rs['id'];
    }

    // *ϴexcelļΪcategory.xls*
    $category_name = basename($_FILES['upload_file']['name'], ".xls");
    $sql = "SELECT `id` FROM `category` WHERE `name` LIKE '$category_name' AND `fid` = 0";
		 //debug($sql);
    $tmp_ary = $db->get_one($sql);
    if (empty($tmp_ary)) {
        $error_str .= "<span class='js_msg'>Category error </span>";
    }
    
    $this_fid = $tmp_ary['id'];
    
    $sql = "SELECT `id`, `fid`, `name`, `code` FROM `category` WHERE `fid` = '$this_fid' ORDER BY `seq` ASC";
    $query = $db->query($sql);
    while($rs = $db->fetch_array($query)) {
        $sub_cat_ary[$rs['code']] = $rs;
        $sub_cat_id_ary[] = $rs['id'];   // ɾproduct_categoryм¼
    }
		//debug($sub_cat_id_ary);

    if (!empty($sub_cat_ary)) {
        foreach ($sub_cat_ary as $k=>$v) {
            $cat_col_ary[$k.' '.$v['name']] = $v['id'];
        }
    } else {
        $cat_col_ary = array();
        //$error_str .= "<div class='js_msg'>no category exist.</div>";
    }

    $category_index = 1;	 
    foreach ($excel_ary[0] as $k=>$v) {
        $v = (string)$v;
        if (preg_match("/(.+[^\s]+)[\s]+$/i", $v, $match_ary)) {
            $v = $match_ary[1];
        }
	 //	debug($excel_ary[0],$excel_sql_ary,$cat_col_ary,$excel_category_column);

        if ($k>0 && trim($v) != "") {
            if (!array_key_exists($v, $excel_sql_ary) && !array_key_exists($v, $cat_col_ary) && !array_key_exists($v, $excel_category_column)) {
                $error_str .= "<div class='js_msg'>excel error in line: " . $v . "</div>";
            } elseif (array_key_exists($v, $excel_sql_ary)) {
                $sql_product_ary[$excel_sql_ary[$v]] = array('sql_col'=>$excel_sql_ary[$v], 'col_num'=>$k);
            } elseif (array_key_exists($v, $excel_category_column )) {
                $k = trim($k);
                //$sql_category_ary[] = array('categoryid'=>$cat_col_ary[$v], 'col_num'=>$k);
				$sql_category_ary[$excel_category_column[$v]] = array('sql_col'=>$excel_category_column[$v], 'col_num'=>$k);
            }
        }
    }
//debug($excel_ary);
    /*
    echo "<pre>";
    echo $error_str;   // Ŀ

    //print_r($product_hava_ary);   // вƷcode
    //print_r($excel_sql_ary);
    //print_r($cat_col_ary);


    //print_r($sql_product_ary);
    //print_r($sql_category_ary);

    print_r($excel_ary);
    exit;
    */

    // ==================== ж end ==============================

    if (empty($excel_ary[1])) {
        $error_str .= "<span class='js_msg'>excel error</span>";
    }

    if ($error_str == "") {
        $code_ary = array();
        $pre_product_id = 0;//ѭм¼ǰһƷIDʼΪ0
		
		
		# 临时记录当前处理的全部都有值的行key值
		$cur_key = 0;
		foreach ($excel_ary as $k=>$v)
    {
				
				if ($k>0 && ( (isset($v[0]) && trim($v[0]) != "") ||  (isset($v[count($excel_sql_ary)]) && trim($v[count($excel_sql_ary)]) != "") )) {
						$cur_key = $k;
				}else{
					if($k != '0'){
						//echo '第'.$k.'行，跟随第'.$cur_key.'行<br>';
						$code = (string)$excel_ary[$cur_key][0];
						$v[$sql_category_ary['category_id']['col_num']] = $cat_col_ary[$v[$sql_category_ary['category_id']['col_num']]];
						$t_seq = $v[$sql_category_ary['seq']['col_num']];
						$t_product_id = $product_hava_ary[strtolower($code)];
						$t_category_id = $v[$sql_category_ary['category_id']['col_num']];
					//	debug($t_category_id);
						//debug($k,$v,$excel_ary[$cur_key],$product_hava_ary[strtolower($code)]);
					//	debug($t_product_id,$t_seq,$v);
						$t_sql = "INSERT INTO `product_category` (`product_id`, `category_id`, `seq`) VALUES ('$t_product_id', '$t_category_id', '$t_seq')";
						$db->query($t_sql);
					}
				}
				
            if ($k>0 && ( (isset($v[0]) && trim($v[0]) != "") ||  (isset($v[count($excel_sql_ary)]) && trim($v[count($excel_sql_ary)]) != "") )){
								
                $code = (string)$v[0];
			
                if (in_array($code, $code_ary) && $code!="") {   // code ΨһԼ
                    $error_str .= "<div class='js_msg'>Code " . $code . " (" . $v[1] . ") have set.</div>";
                } else {
                    $code_ary[] = $code;
					//Ϊµexcelʽ:ͬһƷͬڶʱ
                    if ((!empty($sql_product_ary) && trim($v[0]) != "") || (trim($v[0]) == "" && trim($v[count($excel_sql_ary)]) != "")) {
                        $v[$sql_product_ary['hidden_flag']['col_num']] = (strtolower($v[$sql_product_ary['hidden_flag']['col_num']]) == 'y') ? 0 : 1;
                        $v[$sql_product_ary['new_flag']['col_num']] = (strtolower($v[$sql_product_ary['new_flag']['col_num']]) == 'y') ? 1 : 0;
                        $v[$sql_product_ary['pic']['col_num']] = strstr(strtolower($v[$sql_product_ary['pic']['col_num']]), ".jpg") ? $v[$sql_product_ary['pic']['col_num']] : "";
                        $v[$sql_product_ary['small_pic']['col_num']] = strstr(strtolower($v[$sql_product_ary['small_pic']['col_num']]), ".jpg") ? $v[$sql_product_ary['small_pic']['col_num']] : "";
                        //$v[$sql_product_ary['small_pic']['col_num']] = $v[$sql_product_ary['pic']['col_num']] == "" ? "" : "small_" . $v[$sql_product_ary['small_pic']['col_num']];
                        if (!empty($v[$sql_product_ary['small_pic']['col_num']]) && !strpos($v[$sql_product_ary['small_pic']['col_num']], 's-') && $v[$sql_product_ary['pic']['col_num']] == $v[$sql_product_ary['small_pic']['col_num']] ) {
                            if (strpos($v[$sql_product_ary['small_pic']['col_num']], "-s.")) {
                                $v[$sql_product_ary['small_pic']['col_num']] = str_replace("-s.", ".", $v[$sql_product_ary['small_pic']['col_num']]);
                            }
                            $v[$sql_product_ary['small_pic']['col_num']] = "s-" . $v[$sql_product_ary['small_pic']['col_num']];
                        }

                        $v[$sql_product_ary['length2']['col_num']] = is_float($v[$sql_product_ary['length2']['col_num']]) ? $v[$sql_product_ary['length2']['col_num']] : $v[$sql_product_ary['length']['col_num']]/2.54;
                        $v[$sql_product_ary['height2']['col_num']] = is_float($v[$sql_product_ary['height2']['col_num']]) ? $v[$sql_product_ary['height2']['col_num']] : $v[$sql_product_ary['height']['col_num']]/2.54;
                        $v[$sql_product_ary['width2']['col_num']] = is_float($v[$sql_product_ary['width2']['col_num']]) ? $v[$sql_product_ary['width2']['col_num']] : $v[$sql_product_ary['width']['col_num']]/2.54;

                        $v[$sql_product_ary['cuft']['col_num']] = is_float($v[$sql_product_ary['cuft']['col_num']]) ? $v[$sql_product_ary['cuft']['col_num']] : $v[$sql_product_ary['cbm']['col_num']]/0.0283168;
						
						//Category && Seq
						$v[$sql_category_ary['category_id']['col_num']] = $cat_col_ary[$v[$sql_category_ary['category_id']['col_num']]];

						//first letter to upper						
                        $string_tmp = htmlentities($v[$sql_product_ary['name']['col_num']]);
						
						$v[$sql_product_ary['comment']['col_num']] = strtoupper(substr($v[$sql_product_ary['comment']['col_num']],0,1)).substr($v[$sql_product_ary['comment']['col_num']],1);
							
                        // &iexcl;&reg; => 
                        // &iexcl;&macr; => 
                        // &iexcl;&deg;  =>  
                        // &iexcl;&plusmn;  => 
                        if (strstr($string_tmp, '&iexcl;&macr;')) {
                            $string_tmp = @str_replace('&iexcl;&macr;', '\'', $string_tmp);
                            $string_tmp = @str_replace('&iexcl;&reg;', '\'', $string_tmp);
                        }
                        if (strstr($string_tmp, '&iexcl;&deg;')) {
                            $string_tmp = @str_replace('&iexcl;&deg;', '"', $string_tmp);
                            $string_tmp = @str_replace('&iexcl;&plusmn;', '"', $string_tmp);
                        }
						$string_tmp = html_entity_decode($string_tmp);
						
						if(substr($string_tmp,0,1) == '"') $string_tmp = substr($string_tmp,1);
						if(substr($string_tmp,count($string_tmp)-1) == '"') $string_tmp = substr($string_tmp,0,count($string_tmp)-1);
						$string_tmp = strtoupper(substr($string_tmp,0,1)).substr($string_tmp,1);
						
						//first letter to upper
						//$string_tmp = strtoupper(substr($string_tmp,0,1)).substr($string_tmp,1);
						
                        $v[$sql_product_ary['name']['col_num']] = $string_tmp;
						
							
 //echo   "v[19]:".$v[19]."  sql_category_ary:".$sql_category_ary['seq']['col_num']."<br/>";                    
                        /*
                        if (strpos($v[$sql_product_ary['name']['col_num']], "&#") === false) {
                        } else {
                            $v[$sql_product_ary['name']['col_num']] = str_replace("&#8217", "&#39", $v[$sql_product_ary['name']['col_num']]);
                            $v[$sql_product_ary['name']['col_num']] = preg_replace('/\&\#([0-9]+)/e', 'chr(\\1)', $v[$sql_product_ary['name']['col_num']]);
                        }*/

                        //$v[$sql_product_ary['name']['col_num']] = ent2ncr(htmlentities($v[$sql_product_ary['name']['col_num']], ENT_QUOTES));
                        /*
                        if ($code == '31909l/oak') {
                        echo "<pre>";
                        print_r($product_hava_ary);
                        if (array_key_exists(strtolower($code), $product_hava_ary)) {
                        echo "have";
                        } else {
                        echo "no";
                        }
                        exit;
                        }*/
                        if (array_key_exists(trim(strtolower($code)), $product_hava_ary)) {
                            $update_sql_ary = array();
                            foreach ($sql_product_ary as $k1=>$v1) {
                                $update_sql_ary[] = "`" . $v1['sql_col'] . "` = '" . addslashes($v[$v1['col_num']]) . "'";
                            }
                            $sql = "UPDATE `product` SET " . implode(",", $update_sql_ary) . ", `delete_flag`=0, `cate_seq`=$k WHERE `code` = '" . $code . "'";

                            $db->query($sql);
                        } else {

                            $sql_ary_v = $sql_ary_value = array();
                            foreach ($sql_product_ary as $k1=>$v1) {
                                $sql_ary_v[] = "`" . $v1['sql_col'] . "`";
                                $sql_ary_value[] = "'" . addslashes($v[$v1['col_num']]) . "'";
                            }
                            $sql = "INSERT INTO `product` (`code`,`cate_seq`, " . implode(",", $sql_ary_v) . ") VALUES ('" . $code . "', " .$k.",". implode(",", $sql_ary_value) . ")";
					
                            $db->query($sql);
                            $product_hava_ary[strtolower($v[0])] = $db->insert_id();

                        }
                    }
                    if (!empty($sql_category_ary)) {
                        if (array_key_exists(strtolower($code), $product_hava_ary) || (trim($code)=="" && trim($v[count($excel_sql_ary)]) != "")) {
                       
                            if (!empty($sub_cat_id_ary)) {
                                $sub_cat_id_str = implode(",", $sub_cat_id_ary);
                            }
                            
                            // вƷֻܴһ
                            $sql1 = "DELETE FROM `product_category` WHERE `product_id`='" . $product_hava_ary[strtolower($code)] . "' AND `category_id` IN (" . $sub_cat_id_str . ")";
                            $db->query($sql1);
//                            debug($sql1);
							/*                          
							foreach ($sql_category_ary as $k1=>$v1) {
                                $seq = $v[$v1['col_num']];

                                if ($seq > 0) {
                                    $sql2 = "INSERT INTO `product_category` (`product_id`, `category_id`, `seq`) VALUES ('" . $product_hava_ary[strtolower($code)] . "', '" . $v1['categoryid'] . "', '$seq')";
                                    $db->query($sql2);
                                }
                            }
							*/
/*  
 */
							$seq = $v[$sql_category_ary['seq']['col_num']];
//							   
 							 //if(strtolower($code) == 33073){ 
							 //	debug( $v,$sql_category_ary['category_id']['col_num'] );
 							 //	echo ($seq > 0 && trim($v[0]) != "");
 								//echo "INSERT INTO `product_category` (`product_id`, `category_id`, `seq`) VALUES ('" . $product_hava_ary[strtolower($code)] . "', '" . $v[$sql_category_ary['category_id']['col_num']] . "', '$seq')".'<br />';
 							// }
							 
							 
							if ($seq > 0 && trim($v[0]) != "") {
								$sql2 = "INSERT INTO `product_category` (`product_id`, `category_id`, `seq`) VALUES ('" . $product_hava_ary[strtolower($code)] . "', '" . $v[$sql_category_ary['category_id']['col_num']] . "', '$seq')";
								$db->query($sql2);				
							}
							//ͬһƷͬͬ
							else if($seq > 0 && trim($v[0]) == ""){
						 	
								$sql2 = "INSERT INTO `product_category` (`product_id`, `category_id`, `seq`) VALUES ('" . $pre_product_id . "', '" . $v[$sql_category_ary['category_id']['col_num']] . "', '$seq')";
								$db->query($sql2);				
										
							}
							$pre_product_id = $product_hava_ary[strtolower($code)];
                        }
                    } else {
                        //$this_fid
                        if (array_key_exists(strtolower($code), $product_hava_ary)) {
                            $sql1 = "DELETE FROM `product_category` WHERE `product_id`='" . $product_hava_ary[strtolower($code)] . "' AND `category_id`='" . $this_fid . "'";	
                            $db->query($sql1);

                            $sql2 = "INSERT INTO `product_category` (`product_id`, `category_id`, `seq`) VALUES ('" . $product_hava_ary[strtolower($code)] . "', '" . $this_fid . "', '$category_index')";
					
                            $db->query($sql2);

                            $category_index += 1;
                        }
                    }//line 313
                }//202:else end
            }//198:  if ($k>0 && isset($v[0]) && trim($v[0]) != "") 
        } //196: foreach ($excel_ary as $k=>$v)
		
		//excelĲƷ¼	
		$sql="select id,code from product where id in(select  product_id from `product_category` where category_id in (".$sub_cat_id_str."))";//ԭĲƷ
		$query = $db->query($sql);
		$product_in_category = array();
		while($rs = $db->fetch_array($query)) 
		{
			$product_in_category[$rs["code"]] = $rs["id"];
		}
		
		foreach($product_in_category as $product_code => $product_id)
		{
			if(!in_array(strtolower($product_code),$code_ary) && !in_array(strtoupper($product_code),$code_ary))
			{
				$sql3 = "update `product` set hidden_flag = 1 where `id` = $product_id";
				$db->query($sql3);
			}
		}
    }//193: if ($error_str == "")
	
	
	
	
    // ================= end import excel ==========================


} elseif ($importtype == '2' && $error_str == "") {

    // ============================= import inventory data start==========================
    if ($excel_ary[0][1] != "Qty") {
        $error_str = "<div class='js_msg'>This isn't a inventory excel !</div>";
    } else {
        //if ($excel_ary[0][2] == "HKWHSP(USD)" && $excel_ary[0][3] == "HKWHSP(HKD)") {
		if ($excel_ary[0][2] == "Special Price" && $excel_ary[0][3] == "Special Price2") {
            foreach ($excel_ary as $k=>$v) {
                if ($k>=1 && isset($v[0])  && trim($v[0])!= '' && is_numeric($v[1]) && $v[1]>=0) {
                    if (!isset($v[2])) {
                        $v[2] = 0;
                    }
                    if (!isset($v[3])) {
                        $v[3] = 0;
                    }
                    $v[0] = strtolower($v[0]);
                    $id_ary[] = $v[0];
                    $code_ary[$v[0]]['code'] = $v[0];
                    $code_ary[$v[0]]['qty'] = $v[1];
                    $code_ary[$v[0]]['sp1'] = $v[2];
                    $code_ary[$v[0]]['sp2'] = $v[3];
					$code_ary[$v[0]]['inventory_name'] = trim($v[4]);
					$inventory_name_ary[] = trim($v[4]);
                }
            }

            //$inventory_name_ary = array(0=>"Indonesia Warehouse",1=>"Singapore Show Container");
            //echo "<pre>";print_r($code_ary);//exit;
            $id_ary = array_unique($id_ary);
            $inventory_name_ary = array_unique($inventory_name_ary);
            $inventory_name_str = implode("', '", $inventory_name_ary);
            $inventory_name_str = "'" . $inventory_name_str . "'";
            $sql = "SELECT `id`, `name` FROM `inventory`";
	
            $query = $db->query($sql);
            while($rs = $db->fetch_array($query)) {
                $inventory_name_id_ary[$rs['name']] = $rs['id'];
            }

            $id_code_ary = array();
            $have_set_ary = array();
            $code_tmp_ary = array();

            if (!empty($id_ary)) {
                $id_str = implode("', '", $id_ary);//code X
                $id_str = "'" . $id_str . "'";
                $sql = "SELECT `id`, `code` FROM `product` WHERE `code` IN (" . $id_str . ")";
                $query = $db->query($sql);
                while($rs = $db->fetch_array($query)) {
                    $id_ary2[] = $rs['id'];
                    $rs['code'] = strtolower($rs['code']);
                    $code_tmp_ary[] = $rs['code'];   //дڵcode飬жЩcodeڵ
                    $id_code_ary[$rs['id']] = $code_ary[$rs['code']];
                    //$id_code_ary[$rs['id']]['inventory_id'] = $inventory_name_id_ary[$id_code_ary[$rs['id']]['inventory_name']];
                    $id_code_ary[$rs['id']]['inventory_id'] = $inventory_name_id_ary[$code_ary[$rs['code']]['inventory_name']];
                }

                // жЩcode
                $no_code_ary = '';
                foreach ($id_ary as $k=>$v) {
                    if(!in_array($v, $code_tmp_ary)) {
                        $error_str .= $v . "<br>\r\n";
                    }
                }
                if (!empty($error_str)) {
                    $error_str = "<div class='js_msg'>code not exist:</div><br><br>" . $error_str;
                }

                if (!empty($id_ary2)) {
                    // жЩƷĳinventoryqtyѴ
                    $sql = "SELECT `product_id`, `inventory_id` FROM `product_inventory` WHERE `product_id` IN (" . implode(",", $id_ary2) . ")";

                    $query = $db->query($sql);
                    while($rs = $db->fetch_array($query)) {
                        $have_set_ary[] = $rs['product_id'] . "_" . $rs['inventory_id'];
                    }
                    $have_set_ary = array_unique($have_set_ary);
                }

            }
            // ============ ݸʽΪ end ==========================
            if (!empty($id_code_ary)) {
                foreach ($id_code_ary as $k=>$v) {
                    if (in_array($k . "_" . $v['inventory_id'], $have_set_ary)) {
                        $sql = "UPDATE `product_inventory` SET `qty` = '" . $v['qty'] . "', `special_price` ='" . $v['sp1'] . "', `special_price2` ='" . $v['sp2'] . "'  WHERE `product_id` = '$k' AND `inventory_id`= '" . $v['inventory_id'] . "';";			
                        $query = $db->query($sql);
                    } else {
                        $sql = "INSERT INTO `product_inventory` (`product_id`, `inventory_id`, `qty`, `special_price`, `special_price2`) VALUES ('$k', '" . $v['inventory_id'] . "', '" . $v['qty'] . "', '" . $v['sp1'] . "', '" . $v['sp2'] . "');";
                        $query = $db->query($sql);
                    }
                }
            }

            //
            $sql = "UPDATE `inventory` SET `update` = '$timestamp' WHERE `name` IN (" . $inventory_name_str . ")";
            $query = $db->query($sql);

        } else {
            // ============ ݸʽΪ start ========================
            foreach ($excel_ary as $k=>$v) {
                if ($k>=1 && isset($v[0]) && trim($v[0])!= '' && is_numeric($v[1]) && $v[1]>=0 && $v[3] != '') {
                    if (!isset($v[2])) {
                        $v[2] = 0;
                    }


                    $v[0] = strtolower($v[0]);
                    $v[2] = strtolower($v[2]) == 'nil' ? 0 : $v[2];
                    $id_ary[] = $v[0];
                    $code_ary[$v[0]]['code'] = $v[0];
                    $code_ary[$v[0]]['qty'] = $v[1];
                    $code_ary[$v[0]]['sp'] = $v[2];
                    $code_ary[$v[0]]['inventory_name'] = trim($v[3]);
                    $inventory_name_ary[] = trim($v[3]);
                }
            }

            //echo "<pre>";print_r($inventory_name_ary);exit;
            $id_ary = array_unique($id_ary);
            $inventory_name_ary = array_unique($inventory_name_ary);
            $inventory_name_str = implode("', '", $inventory_name_ary);
            $inventory_name_str = "'" . $inventory_name_str . "'";
            $sql = "SELECT `id`, `name` FROM `inventory`";
            $query = $db->query($sql);
            while($rs = $db->fetch_array($query)) {
                $inventory_name_id_ary[$rs['name']] = $rs['id'];
            }

            $id_code_ary = array();
            $have_set_ary = array();
            $code_tmp_ary = array();

            if (!empty($id_ary)) {
                $id_str = implode("', '", $id_ary);
                $id_str = "'" . $id_str . "'";
                $sql = "SELECT `id`, `code` FROM `product` WHERE `code` IN (" . $id_str . ")";
                $query = $db->query($sql);
                while($rs = $db->fetch_array($query)) {
                    $id_ary2[] = $rs['id'];
                    $rs['code'] = strtolower($rs['code']);
                    $code_tmp_ary[] = $rs['code'];   //дڵcode飬жЩcodeڵ
                    $id_code_ary[$rs['id']] = $code_ary[$rs['code']];
                    $id_code_ary[$rs['id']]['inventory_id'] = $inventory_name_id_ary[$code_ary[$rs['code']]['inventory_name']];
                }
                // жЩcode
                $no_code_ary = '';
                foreach ($id_ary as $k=>$v) {
                    if(!in_array($v, $code_tmp_ary)) {
                        $error_str .= $v . "<br>\r\n";
                    }
                }
                if (!empty($error_str)) {
                    $error_str = "<div class='js_msg'>code not exist:</div><br><br>" . $error_str;
                }

                if (!empty($id_ary2)) {
                    // жЩƷĳinventoryqtyѴ
                    $sql = "SELECT `product_id`, `inventory_id` FROM `product_inventory` WHERE `product_id` IN (" . implode(",", $id_ary2) . ")";

                    $query = $db->query($sql);
                    while($rs = $db->fetch_array($query)) {
                        $have_set_ary[] = $rs['product_id'] . "_" . $rs['inventory_id'];
                    }
                    $have_set_ary = array_unique($have_set_ary);
                }

            }
            // ============ ݸʽΪ end ==========================
            if (!empty($id_code_ary)) {
                foreach ($id_code_ary as $k=>$v) {
                    if (in_array($k . "_" . $v['inventory_id'], $have_set_ary)) {
                        $sql = "UPDATE `product_inventory` SET `qty` = '" . $v['qty'] . "', `special_price` ='" . $v['sp'] . "'  WHERE `product_id` = '$k' AND `inventory_id`= '" . $v['inventory_id'] . "'";
                        $query = $db->query($sql);
                    } else {
                        $sql = "INSERT INTO `product_inventory` (`product_id`, `inventory_id`, `qty`, `special_price`) VALUES ('$k', '" . $v['inventory_id'] . "', '" . $v['qty'] . "', '" . $v['sp'] . "')";	
                        $query = $db->query($sql);
                    }
		        }
            }
            //
            $sql = "UPDATE `inventory` SET `update` = '$timestamp' WHERE `name` IN (" . $inventory_name_str . ")";
            $query = $db->query($sql);

            // ============================= import inventory data end==========================
        }
    }




} elseif ($importtype == '3') {
    if(strtolower(getExt($_FILES['upload_file']['name'])) == "zip" && $_FILES['upload_file']['size'] > 0 && ($_FILES['upload_file']['type'] == "application/x-zip-compressed")){

        require_once '../include/zip.class.php';

        $upload_file = $_FILES['upload_file']['tmp_name'];
        $pic_file_name = "../attachments/" . $_FILES['upload_file']['name'];
        if (!move_uploaded_file($_FILES['upload_file']['tmp_name'], $pic_file_name)) {
            // ļʧ
            $error_str = 'upload zip file failed<br>';
        } else {
            $tmp_file2 = '../attachments/';
            $z=new Zip;
            $z->Extract($pic_file_name, $tmp_file2);
            @unlink($pic_file_name);
        }
    } elseif ($_FILES['upload_file']['size'] > 0) {
        $error_str .= "<div class='js_msg'>picture file error</div>";
    }
}


if ($upload_file != "" && $error_str == '') {
    $error_str = "<span class='red_font'>Import data Succeed</span>";
}

/*
$debug = 1;
debuginfo();
echo "<pre>";
print_r($db->sql_ary);
*/

include_once(JANSEN_ROOT . 'admin/menu_list.php');
require_once PrintEot('a_header');
require_once PrintEot('a_p_import_excel');
require_once PrintEot('a_footer');
?>
